#coding: utf-8 or # -*- coding: utf-8 -*-
import os
import json
from flask import Flask
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy import (create_engine, MetaData, Column, Integer, String)
from sqlalchemy import func
from sqlalchemy.sql.expression import and_, or_
from sqlalchemy import Table, Column, Integer, String, Text, DateTime, MetaData, ForeignKey, Boolean, types, Numeric
from datetime import datetime, date,time
from sqlca import get_Session
from json_exa import ret_json, row3dict
from login import infor_worker
from get_cardinfo import card_ticket_sum,parse_track
from pos_check import token_check,pos_en

from card_app_log import app_log
import sys
reload(sys)
sys.setdefaultencoding('utf8')

Base = declarative_base()


class T_LZ_CARD_SALE_DAY(Base):
    __tablename__ = 'T_LZ_CARD_SALE_DAY'
    C_DT = Column(String(10))
    D_RETAIL_TIME = Column(DateTime, primary_key=True)
    C_SHOP_ID = Column(String(10), primary_key=True)
    C_POS_ID = Column(String(10), primary_key=True)
    C_CASHIER_ID = Column(String(10), primary_key=True)
    C_RETAIL_ID = Column(String(20), primary_key=True)
    C_CARD_ID = Column(String(30), primary_key=True)
    C_TYPE = Column(String(1), primary_key=True)
    C_GATHER_ID = Column(String(3), primary_key=True)
    C_GATHER_NAME = Column(String(10))
    N_MONEY = Column(Numeric(18, 2))
    C_TRANS_FLAG = Column(String(2))
    N_MONEY_BALANCE = Column(Numeric(18, 2))
    C_ACT_ID = Column(String(20))
    D_CON_BEGIN = Column(DateTime)
    D_CON_END = Column(DateTime)
    C_PROCESS_FLAG = Column(String(3))

class T_LZ_TRADE_NO_SEQ(Base):
    __tablename__ = 'T_LZ_TRADE_NO_SEQ'
    c_date = Column(String(10), primary_key=True)
    trade_no  = Column(String(200), primary_key=True)
    cdt = Column(DateTime)
    c_type =  Column(String(1), primary_key=True)
    c_pos_id = Column(String(20))


class T_LZ_CARD_SALE_RETURN(Base):
    __tablename__ = 'T_LZ_CARD_SALE_RETURN'
    C_RETREATE_NO = Column(String(20), primary_key=True)
    C_POS_NO  = Column(String(20)) 
    N_RETREATE_MONEY  =  Column(Numeric(15, 2)) 
    C_CARD_NO  = Column(String(30)) 
    C_RETREATE_DATE  =Column(DateTime) 
    C_WORKER_ID = Column(String(20)) 
    C_RETAIL_NO  = Column(String(20)) 
        


def confirm(data_list):
    # app_log(data_list,'input')
    # return data_list
    if data_list.find('&') <= 0:
        ls_msg = "输入参数错误1"
        return json.dumps({"code": "34", "error": ls_msg}, ensure_ascii=False)

    if data_list.find('posno') <= 0:
        ls_msg = "输入参数错误2"
        return json.dumps({"code": "34", "error": ls_msg},ensure_ascii=False)

    if data_list.find('money') <= 0:
        ls_msg = "输入参数错误3"
        return json.dumps({"code": "34", "error": ls_msg}, ensure_ascii=False)

    if data_list.find('key') <= 0:
        ls_msg = "输入参数错误4"
        return json.dumps({"code": "34", "error": ls_msg}, ensure_ascii=False)

    if data_list.find('old_bill') <= 0:
        ls_msg = "输入参数错误5"
        return json.dumps({"code": "34", "error": ls_msg}, ensure_ascii=False)
    print data_list
    print '--------datalist------'
    list_w = data_list.split('&')
    print list_w
    json_list = {}
    for lst in list_w:
        li = lst.split('=',1)
        json_list[li[0]] = li[1]
    print '----------json_list------'
    print json_list
    token_c = token_check(json_list['key'])
    print token_c
    if token_c['code'] != '10':
        return json.dumps(token_c, ensure_ascii=False)
    oper = token_c['operator']
    #to_ken
    posen = pos_en(json_list['posno'])

    if posen['code'] != '10':
        return json.dumps(posen, ensure_ascii=False)
    shop_id = posen["C_SHOP_ID"]
    old_bill = json_list['old_bill']

    session = get_Session()()
    # oper = json_list['cashier']  to_ken
    query = session.query(func.count(1)).filter(
        infor_worker.C_WORKER_ID == oper).scalar()
    print oper
    oper = oper.decode('gbk')
    if query == 0:
        ls_msg = "操作员%s不存在"%oper
        session.close()
        app_log(json.dumps({"code": "32", "error": ls_msg}, ensure_ascii=False),'output')
        return json.dumps({"code": "32", "error": ls_msg}, ensure_ascii=False)
    ls_type = json_list['req_type']
    lde_money = float( json_list['money'])
    if ls_type == 'pay':
        
        if lde_money < 0:
            ls_msg = "参数错误金额应为正数"
            return json.dumps({"code": "32", "error": ls_msg},ensure_ascii=False)
    else:
        if len(old_bill) == 0:
            ls_msg = "退货单号为空"
            return json.dumps({"code": "32", "error": ls_msg},ensure_ascii=False)
             
        if lde_money > 0:
            ls_msg = "参数错误金额应为负数"
            return json.dumps({"code":"70", "error":ls_msg},ensure_ascii=False)
        ll_count = session.query(func.count(1)).filter(T_LZ_CARD_SALE_RETURN.C_RETREATE_NO==old_bill).scalar()
        if ll_count > 0:
            ls_msg = '订单号无效,不能再次退款'
            return json.dumps({"code": "72", "error": ls_msg},ensure_ascii=False)   
                    

    track_list = parse_track(json_list['track1'],json_list['track2'],json_list['track3'])
    if track_list['code'] != '10':
        session.close()
        app_log(json.dumps(track_list,ensure_ascii=False),'output')
        return json.dumps(track_list,ensure_ascii=False)
    ls_cardno = track_list['card_id']
    ls_cardtype = track_list['cardtype']
    ls_gather_id = session.execute(
        "select C_GATHER_ID from card_property where c_cardtype_id = :ls_typeid",{"ls_typeid":ls_cardtype}).first()[0]
    ret = card_mon(ls_cardno,lde_money,ls_gather_id)
    if ret['code'] != '10':
        session.close()
        #app_log(json.dumps(ret,ensure_ascii=False),'output')
        return json.dumps(ret,ensure_ascii=False)
  
    print "commit card"
    ret_card = confirm_card(json_list,ls_cardno,oper,shop_id,T_LZ_CARD_SALE_DAY,T_LZ_CARD_SALE_RETURN,ls_gather_id,session)
    if ret_card['code'] != '10':
        session.close()
        #app_log(json.dumps(ret_card,ensure_ascii=False),'output')
        print ret_card
        return json.dumps(ret_card,ensure_ascii=False)
    print ret_card['code']
    ls_ret = json.dumps(ret_card,ensure_ascii=False)
    print ls_ret
    app_log(ls_ret,'output')
    return ls_ret



def confirm_card(json_list,cardno,oper,shop_id,T_LZ_CARD_SALE_DAY,T_LZ_CARD_SALE_RETURN,ls_gatherno,session):
    try:

        ls_card = cardno
        ls_gather = ls_gatherno
        # ls_gather_name = '现金储值'.decode('gbk')
        ls_gather_name = '现金储值'
        lde_money = float( json_list['money'])
        pos_no = json_list['posno']
        dtime = session.execute("select (sysdate) from DUAL").first()[0]
        dt = dtime.strftime("%Y-%m-%d") 
        ddt = dtime.strftime("%y%m%d") 
        ls_retail = get_retail_no(pos_no,T_LZ_CARD_SALE_DAY,session) 
        print ls_retail
        T_LZ_CARD = T_LZ_CARD_SALE_DAY(C_DT=dt, D_RETAIL_TIME=dtime, 
        C_SHOP_ID=shop_id, C_POS_ID=pos_no, 
        C_CASHIER_ID=oper, C_RETAIL_ID=ls_retail, C_TYPE='3', 
        C_GATHER_ID=ls_gather, C_GATHER_NAME=ls_gather_name,
        N_MONEY=lde_money, C_CARD_ID=ls_card, 
        N_MONEY_BALANCE=0, C_ACT_ID='*',
        D_CON_BEGIN=dtime, D_CON_END=dtime, 
        C_PROCESS_FLAG='1',C_TRANS_FLAG='1')
        
        session.add(T_LZ_CARD)
        session.flush()
        session.commit()
        ls_type = json_list['req_type']
        if ls_type == 'res':
            t_lzcard_ret =  T_LZ_CARD_SALE_RETURN(C_RETREATE_NO = json_list['old_bill'],C_POS_NO =pos_no ,
                N_RETREATE_MONEY = lde_money, C_CARD_NO =ls_card,C_RETREATE_DATE = dtime , C_WORKER_ID =oper,C_RETAIL_NO =ls_retail  )
            session.add(t_lzcard_ret)
            session.flush()
            session.commit()
        
        query = session.query(card_ticket_sum).filter(card_ticket_sum.C_CARD_ID==ls_card , card_ticket_sum.C_GATHER_ID == ls_gather ).scalar()
        lde_banance = str(query.N_BALANCE)
         
        posno =  pos_no
        billno = ddt+pos_no+ls_retail
        ls_type = json_list['req_type']
        if ls_type == 'pay':
            type = '消费'
        else:
            type = '退货'
        money = str(lde_money)
        
        print_str = '''
        红博商业     
终端机号 %s    
操作员号 %s 
交易时间 %s
订 单 号 %s
交易类型 %s
交易金额 %s
交易卡号 %s
余额     %s
持卡人签字

__________________
持卡人已接受此单据
金额的有关商品
            '''%(posno,oper,dt,billno,type,money,ls_card,lde_banance)
        ls_msg = "succeed"
        return {"code": "10", "error": ls_msg,"print":print_str,"balance":lde_banance,"cardno":ls_card,'money':money}  
    except Exception, e:
        session.rollback()
        error_str = Exception,":",e 
        # app_log(error_str,'output')
        #error_str = error_str.decode('gbk')
        print error_str 
        ls_msg = str(error_str)
        ls_msg = ls_msg.decode('gbk')
        return {"code": "67", "error": ls_msg} 
       
        # return Exception,":",e   


def card_mon(card_id , money , gather):
    session = get_Session()()
    dt = session.execute("select sysdate  from DUAL").first()[0]
    ll_count = session.query(func.count(1)).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.D_CON_END > dt).scalar()
    if ll_count == 0:
        ls_msg = "该卡没有可用余额" 
        session.close()
        return {"code": "60", "error": ls_msg}
    ll_count = session.query(func.count(1)).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.C_GATHER_ID == gather ,card_ticket_sum.N_BALANCE >= money
        ,card_ticket_sum.D_CON_END > dt).scalar()
    print '-----------------'
    print card_id 
    print gather
    print str(money)
    print str(dt)
    if ll_count == 0:
        ls_msg = "余额不足01"
        session.close()
        return {"code": "61", "error": ls_msg} 
    ls_msg = 'succeed'
    return {"code": "10", "error": ls_msg}    


def get_retail_no2(pos_no,session):
    dt = session.execute(
        "select to_char(sysdate,'yyyy-mm-dd') from DUAL").first()[0]
    serial = session.execute(
        "begin F_GET_SERIAL_SEQ(:v_date); end;",{"v_date":dt})
    return serial


def get_retail_no(pos_no,T_LZ_CARD_SALE_DAY,session):
    trade_serial = session.query(func.count(1)).filter(
        T_LZ_CARD_SALE_DAY.C_POS_ID == pos_no).scalar()
    trade_serial = trade_serial+1
    serial = str(trade_serial)
    serial = serial.zfill(5)
    return serial


# session = get_Session()()
# ls_str= 'req_type=res&posno=7108&old_bill=1603158200100001&key=2016-03-1610:03:13,82001,70025&money=-1&track1=*&track2=7183000082&track3=117451164070445'
# print '======d========='
# print confirm(ls_str)
# print '=======d========'


# card_id = '58010100001669'
# gather = '024'
# money = 6.0
# dt = '2016-03-08 17:07:37'
# ll_count = session.query(func.count(1)).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.C_GATHER_ID == gather ,card_ticket_sum.N_BALANCE > money
#         ,card_ticket_sum.D_CON_END > dt).scalar()
# print ll_count


# posno = '32344'
# oper = '34'
# dt = '2016-12-12'
# billno = '1604231564'
# type = '消费'
# money = '22.22'

# print_str = '''
#         红博商业     
# 终端机号 %s    
# 操作员号 %s 
# 交易时间 %s
# 订 单 号 %s
# 交易类型 %s
# 交易金额 %s
# 持卡人签字

# __________________
# 持卡人已接受此单据
# 金额的有关商品
#     '''%(posno,oper,dt,billno,type,money)

# print print_str    


# result = DBSession.execute('begin getlogin(:username, :password); end;',
#                              {'username':request.POST['username'],'password':request.POST['password']});

# session = get_Session()()
# print get_retail_no('111',T_LZ_CARD_SALE_DAY,session)
# infor = '324'
# infor ='req_type=pay&req_gather=1&posno=1233&cashier=2323&old_bill =''&key=rtedfg435dgdfg&req_data={ "gather_id": "03", "gather_name": "脧脰陆冒麓垄脰碌", "card_id": "7181015571", "money": 30}'
# # infor ='req_type=pay&req_gather=1&posno=99999&cashier=2323&old_bill =''&key=rtedfg435dgdfg&req_data={ "gather_id": "03", "gather_name": "脧脰陆冒麓垄脰碌", "card_id": "7181015571", "money": 30}'

# print confirm(infor)
# session = get_Session()()
# card_id = '7181015575'

# dtime = session.execute("select (sysdate) from DUAL").first()[0]
# print dtime
# dt = dtime.strftime("%y-%m-%d")
# print dt

# import datetime
# now = datetime.datetime.now()
# print now
# print now.strftime('%y-%m-%d')




# print dt
# # query = session.query(card_ticket_sum).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.D_CON_END > sysdate).all()
# # print query 
# # for i in query:
# # 	print i.C_CARD_ID
# # 	print i.N_BALANCE
# # 	print i.D_CON_END

# ls_shop = '7113'
# query = session.query(card_ticket_sum).filter(card_ticket_sum.C_CARD_ID==card_id , card_ticket_sum.D_CON_END > dt , card_ticket_sum.C_SHOP_ID == ls_shop).all()

# for i in query:
# 	print i.C_CARD_ID
# 	print i.N_BALANCE
# 	print i.D_CON_END





        # T_LZ_CARD_SALE_DAY2 = T_LZ_CARD_SALE_DAY(C_DT='2016-01-26', D_RETAIL_TIME=dtime, 
        # C_SHOP_ID='7111', C_POS_ID='99999', 
        # C_CASHIER_ID='001', C_RETAIL_ID='00005', C_TYPE='3', 
        # C_GATHER_ID='03', C_GATHER_NAME='脧脰陆冒麓垄脰碌'.decode('gbk'),
        # N_MONEY=10, C_CARD_ID='7183000082', 
        # N_MONEY_BALANCE=0, C_ACT_ID='*',
        # C_TRANS_FLAG = '*',
        # D_CON_BEGIN=dtime, D_CON_END=dtime, 
        # C_PROCESS_FLAG='*')


        # T_LZ_CARD_SALE2 = T_LZ_CARD_SALE_DAY(C_DT='2016-01-26', D_RETAIL_TIME=dtime, 
        # C_SHOP_ID='7111', C_POS_ID='99999', 
        # C_CASHIER_ID='001', C_RETAIL_ID='00005', C_TYPE='3', 
        # C_GATHER_ID='03', C_GATHER_NAME='脧脰陆冒麓垄脰碌'.decode('gbk'),
        # N_MONEY=-2, C_CARD_ID='7183000082', 
        # N_MONEY_BALANCE=0, C_ACT_ID='*',
        # C_TRANS_FLAG = '*',
        # D_CON_BEGIN=dtime, D_CON_END=dtime, 
        # C_PROCESS_FLAG='*')
    #     # session.add(T_LZ_CARD_SALE2)
    #     session.flush()
    #     session.commit() 
    # except Exception, e:
    #     session.rollback()
    #     print Exception,":",e  




# ls_str = 'req_type=pay&posno=81001&old_bill%20=*&key=2016-02-0411:02:08,81001,81001&money=0.1&track1=58010100001508710670019645480&track2=58010100001508&track3=710670019645480'
# print confirm(ls_str)